import calendar
import datetime

from django.db.models import QuerySet, Q, Sum, F

from xadmin.models import Log

__author__ = 'cwd'
__date__ = '2018/2/25 14:19'


import os

from xadmin.layout import Main, Fieldset, Row, Side
import xadmin
import json
from django.db import connection, models
from django.db.models.signals import pre_delete, post_delete
from django.dispatch import receiver
from django.utils.translation import ugettext as _

from users.models import UserProfile
from .models import DesignAdList, UserAdFileLibrary, AdVariation, SitesData, AdGoals, DesignAdSite, AdUrl, AdCreative, \
    SitesTempData, SitesChoiseTempData

from utils.HttpUtils import HttpUtils
from utils.Constants import Constants
from users.tasks import postOrUpdateAdData, postOrUpdateSites


class AdCreativeInline(object):
    model = AdCreative
    exclude = ['id', 'user', 'status', 'created_at', 'status', 'name', 'adCampaigns', 'description', 'url']
    extra = 0


class AdUrlInline(object):
    model = AdUrl
    exclude = ['id', 'user', 'created_at', 'adCampaigns', 'status']
    extra = 0


class DesignAdAdmin(object):
    list_display = ('adName', 'adStyle', 'clicks', 'cost', 'cost_impressions', 'ctr', 'avgCpc', 'avgCpm', 'G1', 'ecpa1', 'G2', 'ecpa2', 'G3', 'ecpa3', 'G4', 'ecpa4', 'change_status', 'change_button')
    list_filter = ['choise_time']
    list_per_page = 20
    show_bookmarks = False #屏蔽书签
    search_fields = ['adName']
    show_detail_fields = ['adName'] #显示数据详情
    ordering = ['-cost']
    refresh_times = (3, 5, 7, 10) #每5秒或7秒刷新
    # list_editable = ('priceModel', 'price', 'maxDailyBudget')  # 可编辑
    exclude = ['adUser', 'created_at', 'status', 'id', 'variation_id', 'campaign_id', 'keywords', 'isPost', 'checked', 'cost', 'clicks', 'ctr', 'cost_impressions', 'avgCpc', 'avgCpm', 'choise_time', 'updated_at', 'G1', 'ecpa1', 'G2', 'ecpa2', 'G3', 'ecpa3', 'G4', 'ecpa4'] # 不显示列
    model_icon = 'fa fa-pencil-square-o'  # 修改图标
    filter_horizontal = (
        'geographicLocationList', 'languagesList', 'mondayList', 'categoriesList',
        'tuesdayList', 'wednesdayList', 'thursdayList', 'fridayList', 'saturdayList', 'sundayList'
    )
    style_fields = {'geographicLocationList': 'm2m_transfer', 'categoriesList': 'm2m_transfer', 'languagesList': 'm2m_transfer',
                    'browsersList': 'm2m_transfer', 'operatingSystemList': 'm2m_transfer', 'mobileCarriersList': 'm2m_transfer', 'devicesList': 'm2m_transfer',
                    'mondayList': 'm2m_transfer', 'tuesdayList': 'm2m_transfer', 'wednesdayList': 'm2m_transfer',
                    'thursdayList': 'm2m_transfer', 'fridayList': 'm2m_transfer', 'saturdayList': 'm2m_transfer',
                    'sundayList': 'm2m_transfer'
                    }
    # 求总和
    aggregate_fields = {'ctr': 'avg', 'cost': 'sum', 'cost_impressions': 'sum', 'clicks': 'sum', 'avgCpc': 'avg',
                        'avgCpm': 'avg', 'G1': 'sum', 'ecpa1': 'avg', 'G2': 'sum', 'ecpa2': 'avg', 'G3': 'sum', 'ecpa3': 'avg', 'G4': 'sum', 'ecpa4': 'avg'}

    inlines = [AdUrlInline, AdCreativeInline]

    # 修改布局
    def get_form_layout(self):
        if self.org_obj:
            self.form_layout = (
                Main(
                    Fieldset(_('推广内容'),
                             'adName', 'adStyle', 'categoriesList',
                             ),

                    Fieldset(_('推广地域'),
                             'geographicLocationList',
                             ),
                    Fieldset(_('高级选项'),
                             'languagesList', 'browsersList', 'operatingSystemList', 'mobileCarriersList', 'devicesList',
                             ),
                    Fieldset(_('推广类型'),
                             Row('priceModel', 'price',),
                             'isMaxDailyBudget', 'maxDailyBudget'
                             ),
                    Fieldset(_('推广配置'),
                             Row('impressionsEnabled', 'impressions', ),
                             Row('minutes', ),
                             ),
                    Fieldset(_('更多选项'),
                             'sites',
                             ),
                    Fieldset(_('推广时段'),
                             'adAllTime', 'mondayList', 'tuesdayList', 'wednesdayList', 'thursdayList', 'fridayList', 'saturdayList', 'sundayList',
                             ),
                ),
                Side(

                )
            )
        return super(DesignAdAdmin, self).get_form_layout()

    def queryset(self):
        try:
            if self.user.is_superuser:
                qs = super(DesignAdAdmin, self).queryset()
                return qs
            else:
                # 今天日期
                day = datetime.date.today()
                # 昨天
                yesterday = day - datetime.timedelta(days=1)
                if str(self.request).find('update') < 0 and str(self.request).find('detail') < 0:
                    if self.request.GET.get('_p_choise_time__year') is not None and self.request.GET.get('_p_choise_time__gte') is None:
                        gte_time = self.request.GET.get('_p_choise_time__year')\
                                   + '-' + self.request.GET.get('_p_choise_time__month') \
                                   + '-' + self.request.GET.get('_p_choise_time__day')
                        lt_time = gte_time
                    elif self.request.GET.get('_p_choise_time__year') is None and self.request.GET.get('_p_choise_time__gte') is not None:
                        gte_time = self.request.GET.get('_p_choise_time__gte')
                        lt_time = self.request.GET.get('_p_choise_time__lt')
                    else:
                        gte_time = str(datetime.date.today())
                        lt_time = str(datetime.date.today())

                    DesignAdList.objects\
                        .filter(adUser_id=self.user.id)\
                        .update(
                            cost=0.00,
                            cost_impressions=0,
                            clicks=0,
                            avgCpc=0.000,
                            avgCpm=0.000,
                            ctr=0.00,
                            G1=0,
                            ecpa1=0.00,
                            G2=0,
                            ecpa2=0.00,
                            G3=0,
                            ecpa3=0.00,
                            G4=0,
                            ecpa4=0.00,
                            choise_time=gte_time
                    )
                    if self.request.GET.get('_p_choise_time__gte') is not None and str(self.request.GET.get('_p_choise_time__gte')).find(str(yesterday)) >=0:
                        raw_list = DesignAdList.objects.raw(
                            'select c.id,c.adCampaigns_id,convert(SUM(c.cost/c.deliveryRate),decimal(10,2)) as cost,SUM(c.clicks) as clicks, SUM(c.impressions) as impressions,convert(case when SUM(c.impressions)<=0 then 0 else SUM(c.clicks)*100/SUM(c.impressions) end,decimal(10,2)) as ctr,convert(case when SUM(c.clicks)<=0 then 0 else SUM(c.cost/c.deliveryRate)/SUM(c.clicks) end,decimal(10,3)) as avgCpc,convert(case when SUM(c.impressions)<=0 then 0 else SUM(c.cost/c.deliveryRate)*1000/SUM(c.impressions) end,decimal(10,3)) as avgCpm,SUM(c.G1) as G1,CONVERT(case when SUM(c.G1)<=0 then 0 else SUM(c.cost / c.deliveryRate) / SUM(c.G1) end, DECIMAL(10,2)) as ecpa1,SUM(c.G2) as G2,CONVERT(case when SUM(c.G2)<=0 then 0 else SUM(c.cost / c.deliveryRate) / SUM(c.G2) end, DECIMAL(10,2)) as ecpa2,SUM(c.G3) as G3,CONVERT(case when SUM(c.G3)<=0 then 0 else SUM(c.cost / c.deliveryRate) / SUM(c.G3) end, DECIMAL(10,2)) as ecpa3,SUM(c.G4) as G4,CONVERT(case when SUM(c.G4)<=0 then 0 else SUM(c.cost / c.deliveryRate) / SUM(c.G4) end, DECIMAL(10,2)) as ecpa4  from costreport_costdailyreport c where  c.adUser_id=' + str(
                                self.user.id) + ' AND c.date="' + gte_time + '" group by c.adCampaigns_id')
                    else:
                        raw_list = DesignAdList.objects.raw('select c.id,c.adCampaigns_id,convert(SUM(c.cost/c.deliveryRate),decimal(10,2)) as cost,SUM(c.clicks) as clicks, SUM(c.impressions) as impressions,convert(case when SUM(c.impressions)<=0 then 0 else SUM(c.clicks)*100/SUM(c.impressions) end,decimal(10,2)) as ctr,convert(case when SUM(c.clicks)<=0 then 0 else SUM(c.cost/c.deliveryRate)/SUM(c.clicks) end,decimal(10,3)) as avgCpc,convert(case when SUM(c.impressions)<=0 then 0 else SUM(c.cost/c.deliveryRate)*1000/SUM(c.impressions) end,decimal(10,3)) as avgCpm,SUM(c.G1) as G1,CONVERT(case when SUM(c.G1)<=0 then 0 else SUM(c.cost / c.deliveryRate) / SUM(c.G1) end, DECIMAL(10,2)) as ecpa1,SUM(c.G2) as G2,CONVERT(case when SUM(c.G2)<=0 then 0 else SUM(c.cost / c.deliveryRate) / SUM(c.G2) end, DECIMAL(10,2)) as ecpa2,SUM(c.G3) as G3,CONVERT(case when SUM(c.G3)<=0 then 0 else SUM(c.cost / c.deliveryRate) / SUM(c.G3) end, DECIMAL(10,2)) as ecpa3,SUM(c.G4) as G4,CONVERT(case when SUM(c.G4)<=0 then 0 else SUM(c.cost / c.deliveryRate) / SUM(c.G4) end, DECIMAL(10,2)) as ecpa4 from costreport_costdailyreport c where  c.adUser_id='+str(self.user.id)+' AND c.date BETWEEN "'+gte_time+'" AND "'+lt_time+'" group by c.adCampaigns_id')
                    # print(raw_list.query)
                    num = 0
                    for i in raw_list:
                        num += 1

                    if num > 0:
                        for raw_l in raw_list:

                            DesignAdList.objects.filter(id=raw_l.adCampaigns_id, adUser_id=self.user.id).update(
                                cost=raw_l.cost,
                                cost_impressions=raw_l.impressions,
                                clicks=raw_l.clicks,
                                avgCpc=raw_l.avgCpc,
                                avgCpm=raw_l.avgCpm,
                                ctr=raw_l.ctr,
                                G1=raw_l.G1,
                                ecpa1=raw_l.ecpa1,
                                G2=raw_l.G2,
                                ecpa2=raw_l.ecpa2,
                                G3=raw_l.G3,
                                ecpa3=raw_l.ecpa3,
                                G4=raw_l.G4,
                                ecpa4=raw_l.ecpa4,
                            )
                    else:
                        DesignAdList.objects.filter(adUser_id=self.user.id).update(
                            cost=0.00,
                            cost_impressions=0,
                            clicks=0,
                            avgCpc=0.000,
                            avgCpm=0.000,
                            ctr=0.00,
                            G1=0,
                            ecpa1=0.00,
                            G2=0,
                            ecpa2=0,
                            G3=0,
                            ecpa3=0.00,
                            G4=0,
                            ecpa4=0.00,
                        )
                qs = DesignAdList.objects.filter(adUser=self.user)
                return qs
        except Exception as e:
            print(e)

    def save_models(self):
        try:
            obj = self.new_obj
            obj.adUser_id = self.user.id
            obj.save()

            publisher_ad_types = []

            publisher_ad_type_all = self.request.POST.get('publisher_ad_type_all', '')
            publisher_ad_types_2 = self.request.POST.get('publisher_ad_types_2', '')
            publisher_ad_types_10 = self.request.POST.get('publisher_ad_types_10', '')
            publisher_ad_types_9 = self.request.POST.get('publisher_ad_types_9', '')
            publisher_ad_types_17 = self.request.POST.get('publisher_ad_types_17', '')
            publisher_ad_types_27 = self.request.POST.get('publisher_ad_types_27', '')
            publisher_ad_types_20 = self.request.POST.get('publisher_ad_types_20', '')
            publisher_ad_types_23 = self.request.POST.get('publisher_ad_types_23', '')
            publisher_ad_types_25 = self.request.POST.get('publisher_ad_types_25', '')
            publisher_ad_types_24 = self.request.POST.get('publisher_ad_types_24', '')
            publisher_ad_types_14 = self.request.POST.get('publisher_ad_types_14', '')
            publisher_ad_types_3 = self.request.POST.get('publisher_ad_types_3', '')
            publisher_ad_types_12 = self.request.POST.get('publisher_ad_types_12', '')

            if str(obj.adStyle).find('Banner - 300x250') >= 0 \
                    and publisher_ad_type_all is not '' \
                    and int(publisher_ad_type_all) == 1:
                obj.publisher_ad_types = 1
                publisher_ad_types.extend([2, 9, 17, 10, 24, 27])
            elif (str(obj.adStyle).find('Banner - 120x600') >= 0
                  or str(obj.adStyle).find('Banner - 160x600') >= 0
                  or str(obj.adStyle).find('Banner - 250x250') >= 0
                  or str(obj.adStyle).find('Banner - 315x300') >= 0
                  or str(obj.adStyle).find('Banner - 900x250') >= 0) \
                    and publisher_ad_type_all is not '' \
                    and int(publisher_ad_type_all) == 1:
                obj.publisher_ad_types = 1
                publisher_ad_types.extend([2, 17])
            elif (str(obj.adStyle).find('Banner - 468x60') >= 0
                  or str(obj.adStyle).find('Banner - 728x90') >= 0) \
                    and publisher_ad_type_all is not '' \
                    and int(publisher_ad_type_all) == 1:
                obj.publisher_ad_types = 1
                publisher_ad_types.extend([2, 17, 27])
            elif str(obj.adStyle).find('Native Ad') >= 0 \
                    and publisher_ad_type_all is not '' \
                    and int(publisher_ad_type_all) == 1:
                obj.publisher_ad_types = 1
                publisher_ad_types.extend([20, 23, 25])
            elif str(obj.adStyle).find('Mobile - 300x50') >= 0 \
                    and publisher_ad_type_all is not ''\
                    and int(publisher_ad_type_all) == 1:
                obj.publisher_ad_types = 1
                publisher_ad_types.extend([10])
            elif str(obj.adStyle).find('Mobile - 300x100') >= 0 \
                    and publisher_ad_type_all is not ''\
                    and int(publisher_ad_type_all) == 1:
                obj.publisher_ad_types = 1
                publisher_ad_types.extend([10, 14])
            elif str(obj.adStyle).find('Popunders') >= 0 \
                    and publisher_ad_type_all is not '' \
                    and int(publisher_ad_type_all) == 1:
                obj.publisher_ad_types = 1
                publisher_ad_types.extend([3, 12])
            elif publisher_ad_type_all is '':
                if publisher_ad_types_2 is not '':
                    publisher_ad_types.append(2)
                if publisher_ad_types_9 is not '':
                    publisher_ad_types.append(9)
                if publisher_ad_types_17 is not '':
                    publisher_ad_types.append(17)
                if publisher_ad_types_10 is not '':
                    publisher_ad_types.append(10)
                if publisher_ad_types_24 is not '':
                    publisher_ad_types.append(24)
                if publisher_ad_types_27 is not '':
                    publisher_ad_types.append(27)
                if publisher_ad_types_20 is not '':
                    publisher_ad_types.append(20)
                if publisher_ad_types_23 is not '':
                    publisher_ad_types.append(23)
                if publisher_ad_types_25 is not '':
                    publisher_ad_types.append(25)
                if publisher_ad_types_12 is not '':
                    publisher_ad_types.append(12)
                if publisher_ad_types_3 is not '':
                    publisher_ad_types.append(3)
                if publisher_ad_types_14 is not '':
                    publisher_ad_types.append(14)

                obj.publisher_ad_types = str(publisher_ad_types).replace('[', '').replace(']', '')
            print(publisher_ad_types)
            obj.save()
            if obj.campaign_id != 1:
                print('PUT')
                postOrUpdateAdData.delay('PUT', obj.id, obj.adUser_id, publisher_ad_types)

            else:
                print('POST')
                # 调用新建广告异步任务
                postOrUpdateAdData.delay('POST', obj.id, obj.adUser_id, publisher_ad_types)
        except Exception as e:
            print(e)

    @receiver(pre_delete, sender=DesignAdList)
    def ad_delte(sender, instance, **kwargs):
        try:
            print('删除')
            print(instance.campaign_id)
            isDel = HttpUtils.putHttp(Constants.CAMPAIGNS_DELETE_URL, {"campaign_ids": [instance.campaign_id]}, instance.adUser_id)
            if isDel is not None:
                if isDel.status_code == 200:
                    print('删除成功！！！')
        except Exception as e:
            print(e)


class UserAdFileLibraryAdmin(object):
    list_display = ('file_name', 'file_id', 'vari_file', 'type', 'width', 'height', 'file_extension', 'file_size')
    list_filter = ('type', 'file_extension', 'created_at')
    list_per_page = 20
    model_icon = 'fa fa-bell-o'
    show_bookmarks = False  # 屏蔽书签
    exclude = ['user', 'file_id', 'id', 'type', 'width', 'height', 'file_extension', 'file_size', 'url', 'localUrl', 'created_at'] # 不显示列
    ordering = ['created_at']
    show_detail_fields = ['file_name']  # 显示数据详情
    search_fields = ['user__username', 'file_name', 'type', 'file_extension']

    def queryset(self):
        try:
            qs = super(UserAdFileLibraryAdmin, self).queryset()
            if self.user.is_superuser:
                return qs
            else:
                myUser = UserProfile.objects.get(id=self.user.id)
                return qs.filter(user=myUser)
        except Exception as e:
            print(e)

    def save_models(self):
        obj = self.new_obj
        obj.user_id = self.user.id
        obj.save()

        log = Log(
            user_id=self.user.id,
            ip_addr=self.request.META['REMOTE_ADDR'],
            action_flag='create',
            message='上传广告创意：{0}'.format(obj.file_name)
        )
        log.save()
        # 上传文件则添加或更新
        try:
            if (obj.adMaterial.url).find('/designad') >= 0:
                filePath = (os.path.dirname(os.path.abspath(__file__))) \
                    .replace('\\', '/') \
                    .replace('/apps/designad', obj.adMaterial.url)
                print(filePath)
                fileIo = open(filePath, 'rb')
                files = {
                    'file': (obj.adMaterial.url.split('/')[5], fileIo, 'multipart/form-data'),
                }
                res = HttpUtils.postHttp(Constants.IMAGE_LIBRARY_URL, files, True, obj.user_id)
                print(res)
                print(res.text)

                # 需要判断请求状态
                if res is not None:
                    if res.status_code == 200:
                        print(res.text)
                        res = res.text.replace("'", '"')
                        fileInfo = json.loads(res)
                        print(fileInfo.get('url'))
                        # 关闭文件
                        fileIo.close()
                        # 删除上传的原始图片
                        # os.remove(filePath)


                        # 保存用户广告素材
                        obj.file_id = fileInfo.get('id')
                        obj.type = fileInfo.get('type')
                        obj.url = fileInfo.get('url')
                        obj.localUrl = filePath
                        obj.width = fileInfo.get('width')
                        obj.height = fileInfo.get('height')
                        obj.file_extension = fileInfo.get('file_extension')
                        obj.height = fileInfo.get('height')
                        obj.file_size = fileInfo.get('file_size_public')
                        obj.save()

                    elif res.status_code == 500:
                        print(obj.adMaterial.url)
                        obj.type = '上传失败'
                        obj.save()
        except Exception as e:
            print(e)
        pass

    # 删除广告内容中对应的广告创意的广告内容
    @receiver(post_delete, sender=UserAdFileLibrary)
    def ad_delte(sender, instance, **kwargs):
        try:
            if UserAdFileLibrary.objects.filter(file_id=instance.file_id).count() <= 0:
                AdVariation.objects.filter(id_library_file=instance.file_id, user_id=instance.user_id).delete()
                AdCreative.objects.filter(id_library_file=instance.file_id).delete()
        except Exception as e:
            print(e)

    # def has_delete_permission(request, obj=None):
    #     return False

    def has_change_permission(request, obj=None):
        return False


class AdVariationAdmin(object):
    list_display = ('vari_file',  'clicks', 'cost', 'impressions', 'ctr', 'avgCpc', 'avgCpm', 'G1', 'ecpa1', 'G2', 'ecpa2', 'G3', 'ecpa3', 'G4', 'ecpa4', 'change_status', 'change_button')
    list_filter = ['adCampaigns', 'choise_time']
    search_fields = ['user__username', 'name', 'adCampaigns__adName', 'url', 'id_library_file']
    list_per_page = 20
    show_bookmarks = False  # 屏蔽书签
    ordering = ['-cost']
    model_icon = 'fa fa-file-word-o'
    show_detail_fields = ['vari_file']  # 显示数据详情
    exclude = ['id', 'name', 'varId', 'user', 'status', 'url_id', 'creative_id', 'description', 'vari_url', 'created_at', 'clicks', 'cost', 'impressions', 'ctr', 'avgCpc', 'avgCpm', 'choise_time']  # 不显示列
    # 求总和
    aggregate_fields = {'ctr': 'avg', 'cost': 'sum', 'impressions': 'sum', 'clicks': 'sum', 'avgCpc': 'avg',
                        'avgCpm': 'avg', 'G1': 'sum', 'ecpa1': 'avg', 'G2': 'sum', 'ecpa2': 'avg', 'G3': 'sum', 'ecpa3': 'avg', 'G4': 'sum', 'ecpa4': 'avg'}

    def save_models(self):
        obj = self.new_obj
        obj.save()

    def queryset(self):
        try:
            if self.user.is_superuser:
                qs = super(AdVariationAdmin, self).queryset()
                return qs
            else:
                # 今天日期
                day = datetime.date.today()
                # 昨天
                yesterday = day - datetime.timedelta(days=1)
                # 明天
                tomorrow = day + datetime.timedelta(days=1)

                if self.request.GET.get('_p_choise_time__year') is not None:
                    gte_time = self.request.GET.get('_p_choise_time__year') \
                               + '-' + self.request.GET.get('_p_choise_time__month') \
                               + '-' + self.request.GET.get('_p_choise_time__day')
                    lt_time = gte_time
                elif self.request.GET.get('_p_choise_time__gte') is not None:
                    gte_time = self.request.GET.get('_p_choise_time__gte')
                    lt_time = self.request.GET.get('_p_choise_time__lt')
                else:
                    gte_time = day
                    lt_time = tomorrow

                AdVariation.objects.filter(user_id=self.user.id).update(
                    cost=0.00,
                    impressions=0,
                    clicks=0,
                    avgCpc=0.000,
                    avgCpm=0.000,
                    ctr=0.00,
                    G1=0,
                    ecpa1=0.00,
                    G2=0,
                    ecpa2=0.00,
                    G3=0,
                    ecpa3=0.00,
                    G4=0,
                    ecpa4=0.00,
                    choise_time=gte_time
                )
                if self.request.GET.get('_p_choise_time__gte') is not None and str(
                        self.request.GET.get('_p_choise_time__gte')).find(str(yesterday)) >= 0:
                    raw_list = AdVariation.objects.raw(
                        'select c.id,c.id_variation,convert(SUM(c.cost/c.deliveryRate),decimal(10,2)) as cost,SUM(c.clicks) as clicks, SUM(c.impressions) as impressions,convert(case when SUM(c.impressions)<=0 then 0 else SUM(c.clicks)*100/SUM(c.impressions) end,decimal(10,2)) as ctr,convert(case when SUM(c.clicks)<=0 then 0 else SUM(c.cost/c.deliveryRate)/SUM(c.clicks) end,decimal(10,3)) as avgCpc,convert(case when SUM(c.impressions)<=0 then 0 else SUM(c.cost/c.deliveryRate)*1000/SUM(c.impressions) end,decimal(10,3)) as avgCpm,SUM(c.G1) as G1,CONVERT(case when SUM(c.G1)<=0 then 0 else SUM(c.cost / c.deliveryRate) / SUM(c.G1) end, DECIMAL(10,2)) as ecpa1,SUM(c.G2) as G2,CONVERT(case when SUM(c.G2)<=0 then 0 else SUM(c.cost / c.deliveryRate) / SUM(c.G2) end, DECIMAL(10,2)) as ecpa2,SUM(c.G3) as G3,CONVERT(case when SUM(c.G3)<=0 then 0 else SUM(c.cost / c.deliveryRate) / SUM(c.G3) end, DECIMAL(10,2)) as ecpa3,SUM(c.G4) as G4,CONVERT(case when SUM(c.G4)<=0 then 0 else SUM(c.cost / c.deliveryRate) / SUM(c.G4) end, DECIMAL(10,2)) as ecpa4  from designad_ad_variation_data c where c.user_id=' + str(
                            self.user.id) + ' AND c.date="' + str(
                            gte_time) + '" group by c.id_variation')
                else:
                    raw_list = AdVariation.objects.raw(
                        'select c.id,c.id_variation,convert(SUM(c.cost/c.deliveryRate),decimal(10,2)) as cost,SUM(c.clicks) as clicks, SUM(c.impressions) as impressions,convert(case when SUM(c.impressions)<=0 then 0 else SUM(c.clicks)*100/SUM(c.impressions) end,decimal(10,2)) as ctr,convert(case when SUM(c.clicks)<=0 then 0 else SUM(c.cost/c.deliveryRate)/SUM(c.clicks) end,decimal(10,3)) as avgCpc,convert(case when SUM(c.impressions)<=0 then 0 else SUM(c.cost/c.deliveryRate)*1000/SUM(c.impressions) end,decimal(10,3)) as avgCpm,SUM(c.G1) as G1,CONVERT(case when SUM(c.G1)<=0 then 0 else SUM(c.cost / c.deliveryRate) / SUM(c.G1) end, DECIMAL(10,2)) as ecpa1,SUM(c.G2) as G2,CONVERT(case when SUM(c.G2)<=0 then 0 else SUM(c.cost / c.deliveryRate) / SUM(c.G2) end, DECIMAL(10,2)) as ecpa2,SUM(c.G3) as G3,CONVERT(case when SUM(c.G3)<=0 then 0 else SUM(c.cost / c.deliveryRate) / SUM(c.G3) end, DECIMAL(10,2)) as ecpa3,SUM(c.G4) as G4,CONVERT(case when SUM(c.G4)<=0 then 0 else SUM(c.cost / c.deliveryRate) / SUM(c.G4) end, DECIMAL(10,2)) as ecpa4  from designad_ad_variation_data c where c.user_id=' + str(
                            self.user.id) + ' AND c.date BETWEEN "' + str(
                            gte_time) + '" AND "' + str(
                            lt_time) + '" group by c.id_variation')
                # print(raw_list.query)
                num = 0
                for i in raw_list:
                    num += 1
                if num > 0:
                    for raw_l in raw_list:
                        if raw_l.cost is not None and raw_l.impressions is not None and raw_l.clicks is not None:
                            AdVariation.objects.filter(varId=raw_l.id_variation, user_id=self.user.id).update(
                                cost=raw_l.cost,
                                impressions=raw_l.impressions,
                                clicks=raw_l.clicks,
                                avgCpc=raw_l.avgCpc,
                                avgCpm=raw_l.avgCpm,
                                ctr=raw_l.ctr,
                                G1=raw_l.G1,
                                ecpa1=raw_l.ecpa1,
                                G2=raw_l.G2,
                                ecpa2=raw_l.ecpa2,
                                G3=raw_l.G3,
                                ecpa3=raw_l.ecpa3,
                                G4=raw_l.G4,
                                ecpa4=raw_l.ecpa4,
                            )
                else:
                    AdVariation.objects.filter(user_id=self.user.id).update(
                        cost=0.00,
                        impressions=0,
                        clicks=0,
                        avgCpc=0.000,
                        avgCpm=0.000,
                        ctr=0.00,
                        G1=0,
                        ecpa1=0.00,
                        G2=0,
                        ecpa2=0.00,
                        G3=0,
                        ecpa3=0.00,
                        G4=0,
                        ecpa4=0.00,
                    )
                qs = AdVariation.objects.filter(user=self.user, status__lt=3)
                return qs
        except Exception as e:
            print(e)

    # 屏蔽添加按钮
    def has_add_permission(self):
        return False

    def has_delete_permission(request, obj=None):
        if not request.user.is_superuser:
            return False
        else:
            return True

    def has_change_permission(request, obj=None):
        return False


class SitesDataAdmin(object):
    list_display = ('site_hostname', 'adCampaigns',  'ctr', 'cost', 'impressions', 'clicks', 'avgCpc', 'avgCpm', 'G1', 'ecpa1', 'G2', 'ecpa2', 'G3', 'ecpa3', 'G4', 'ecpa4')
    list_filter = ('adCampaigns', 'date')
    search_fields = ['user__username', 'site_hostname', 'adCampaigns__adName']
    list_per_page = 30
    model_icon = 'fa fa-table'
    ordering = ['-cost']
    show_bookmarks = False  # 屏蔽书签
    show_detail_fields = ['site_hostname']  # 显示数据详情
    exclude = ['cpm', 'cpc', 'cpv', 'deliveryRate', 'date']  # 不显示列
    import_excel = True
    # 求总和
    aggregate_fields = {'ctr': 'avg', 'cost': 'sum', 'impressions': 'sum', 'clicks': 'sum', 'avgCpc': 'avg', 'avgCpm': 'avg', 'G1': 'sum', 'ecpa1': 'avg', 'G2': 'sum', 'ecpa2': 'avg', 'G3': 'sum', 'ecpa3': 'avg', 'G4': 'sum', 'ecpa4': 'avg'}

    def queryset(self):
        try:
            if self.user.is_superuser:
                return super(SitesDataAdmin, self).queryset()
            else:
                # 今天日期
                day = datetime.date.today()
                # 今年一月一号
                day1 = datetime.datetime(datetime.date.today().year, 1, 1)
                # 本月一号
                cur_date_first = datetime.datetime(datetime.date.today().year, datetime.date.today().month, 1)
                # 过去7天
                week_day = day - datetime.timedelta(days=7)
                # 昨天
                yesterday = day - datetime.timedelta(days=1)
                # 明天
                tomorrow = day + datetime.timedelta(days=1)

                if self.request.GET.get('_p_date__year') is not None:
                    gte_time = self.request.GET.get('_p_date__year')\
                               + '-' + self.request.GET.get('_p_date__month') \
                               + '-' + self.request.GET.get('_p_date__day')
                    # 选择日期
                    if self.request.GET.get('_p_adCampaigns__id__exact') is not None:
                        qs = SitesData.objects.filter(user_id=self.user.id,
                                                      adCampaigns_id=self.request.GET.get(
                                                          '_p_adCampaigns__id__exact'), date=gte_time)
                        return qs.extra(select={
                            'cost': 'CONVERT(designad_sitesdata.cost / designad_sitesdata.deliveryRate, DECIMAL(10,2))',
                            'avgCpc': 'CONVERT(designad_sitesdata.avgCpc / designad_sitesdata.deliveryRate, DECIMAL(10,3))',
                            'avgCpm': 'CONVERT(designad_sitesdata.avgCpm / designad_sitesdata.deliveryRate, DECIMAL(10,3))',
                            'ecpa1': 'CONVERT(designad_sitesdata.ecpa1 / designad_sitesdata.deliveryRate, DECIMAL(10,2))',
                            'ecpa2': 'CONVERT(designad_sitesdata.ecpa2 / designad_sitesdata.deliveryRate, DECIMAL(10,2))',
                            'ecpa3': 'CONVERT(designad_sitesdata.ecpa3 / designad_sitesdata.deliveryRate, DECIMAL(10,2))',
                            'ecpa4': 'CONVERT(designad_sitesdata.ecpa4 / designad_sitesdata.deliveryRate, DECIMAL(10,2))',
                        })
                    else:
                        qs = SitesData.objects.filter(user_id=self.user.id,
                                                      date=gte_time
                                                      )
                        return qs.extra(select={
                            'cost': 'CONVERT(designad_sitesdata.cost / designad_sitesdata.deliveryRate, DECIMAL(10,2))',
                            'avgCpc': 'CONVERT(designad_sitesdata.avgCpc / designad_sitesdata.deliveryRate, DECIMAL(10,3))',
                            'avgCpm': 'CONVERT(designad_sitesdata.avgCpm / designad_sitesdata.deliveryRate, DECIMAL(10,3))',
                            'ecpa1': 'CONVERT(designad_sitesdata.ecpa1 / designad_sitesdata.deliveryRate, DECIMAL(10,2))',
                            'ecpa2': 'CONVERT(designad_sitesdata.ecpa2 / designad_sitesdata.deliveryRate, DECIMAL(10,2))',
                            'ecpa3': 'CONVERT(designad_sitesdata.ecpa3 / designad_sitesdata.deliveryRate, DECIMAL(10,2))',
                            'ecpa4': 'CONVERT(designad_sitesdata.ecpa4 / designad_sitesdata.deliveryRate, DECIMAL(10,2))',
                        })

                else:
                    gte_time = self.request.GET.get('_p_date__gte')
                    lt_time = self.request.GET.get('_p_date__lt')

                if gte_time is None:
                    gte_time = day
                if lt_time is None:
                    lt_time = tomorrow

                first_ad = DesignAdList.objects.filter(adUser_id=self.user.id)
                if first_ad.count() > 0:
                    # 今天
                    if str(gte_time).find(str(day)) >= 0 and str(lt_time).find(str(tomorrow)) >= 0:
                        if self.request.GET.get('_p_adCampaigns__id__exact') is not None:
                            qs = SitesData.objects.filter(user_id=self.user.id,
                                                              adCampaigns_id=self.request.GET.get(
                                                                  '_p_adCampaigns__id__exact'), date=day)
                            return qs.extra(select={
                                'cost': 'CONVERT(designad_sitesdata.cost / designad_sitesdata.deliveryRate, DECIMAL(10,2))',
                                'avgCpc': 'CONVERT(designad_sitesdata.avgCpc / designad_sitesdata.deliveryRate, DECIMAL(10,3))',
                                'avgCpm': 'CONVERT(designad_sitesdata.avgCpm / designad_sitesdata.deliveryRate, DECIMAL(10,3))',
                                'ecpa1': 'CONVERT(designad_sitesdata.ecpa1 / designad_sitesdata.deliveryRate, DECIMAL(10,2))',
                                'ecpa2': 'CONVERT(designad_sitesdata.ecpa2 / designad_sitesdata.deliveryRate, DECIMAL(10,2))',
                                'ecpa3': 'CONVERT(designad_sitesdata.ecpa3 / designad_sitesdata.deliveryRate, DECIMAL(10,2))',
                                'ecpa4': 'CONVERT(designad_sitesdata.ecpa4 / designad_sitesdata.deliveryRate, DECIMAL(10,2))',
                            })
                        else:
                            qs = SitesData.objects.filter(user_id=self.user.id,
                                                          date=day
                                                          )
                            return qs.extra(select={
                                'cost': 'CONVERT(designad_sitesdata.cost / designad_sitesdata.deliveryRate, DECIMAL(10,2))',
                                'avgCpc': 'CONVERT(designad_sitesdata.avgCpc / designad_sitesdata.deliveryRate, DECIMAL(10,3))',
                                'avgCpm': 'CONVERT(designad_sitesdata.avgCpm / designad_sitesdata.deliveryRate, DECIMAL(10,3))',
                                'ecpa1': 'CONVERT(designad_sitesdata.ecpa1 / designad_sitesdata.deliveryRate, DECIMAL(10,2))',
                                'ecpa2': 'CONVERT(designad_sitesdata.ecpa2 / designad_sitesdata.deliveryRate, DECIMAL(10,2))',
                                'ecpa3': 'CONVERT(designad_sitesdata.ecpa3 / designad_sitesdata.deliveryRate, DECIMAL(10,2))',
                                'ecpa4': 'CONVERT(designad_sitesdata.ecpa4 / designad_sitesdata.deliveryRate, DECIMAL(10,2))',
                            })
                    # 昨日
                    if str(gte_time).find(str(yesterday)) >= 0 and str(lt_time).find(str(day)) >= 0:
                        if self.request.GET.get('_p_adCampaigns__id__exact') is not None:
                            qs = SitesData.objects.filter(user_id=self.user.id,
                                                              adCampaigns_id=self.request.GET.get(
                                                                  '_p_adCampaigns__id__exact'), date=yesterday)
                            return qs.extra(select={
                                'cost': 'CONVERT(designad_sitesdata.cost / designad_sitesdata.deliveryRate, DECIMAL(10,2))',
                                'avgCpc': 'CONVERT(designad_sitesdata.avgCpc / designad_sitesdata.deliveryRate, DECIMAL(10,3))',
                                'avgCpm': 'CONVERT(designad_sitesdata.avgCpm / designad_sitesdata.deliveryRate, DECIMAL(10,3))',
                                'ecpa1': 'CONVERT(designad_sitesdata.ecpa1 / designad_sitesdata.deliveryRate, DECIMAL(10,2))',
                                'ecpa2': 'CONVERT(designad_sitesdata.ecpa2 / designad_sitesdata.deliveryRate, DECIMAL(10,2))',
                                'ecpa3': 'CONVERT(designad_sitesdata.ecpa3 / designad_sitesdata.deliveryRate, DECIMAL(10,2))',
                                'ecpa4': 'CONVERT(designad_sitesdata.ecpa4 / designad_sitesdata.deliveryRate, DECIMAL(10,2))',
                            })
                        else:
                            qs = SitesData.objects.filter(user_id=self.user.id,
                                                          date=yesterday
                                                          )
                            return qs.extra(select={
                                'cost': 'CONVERT(designad_sitesdata.cost / designad_sitesdata.deliveryRate, DECIMAL(10,2))',
                                'avgCpc': 'CONVERT(designad_sitesdata.avgCpc / designad_sitesdata.deliveryRate, DECIMAL(10,3))',
                                'avgCpm': 'CONVERT(designad_sitesdata.avgCpm / designad_sitesdata.deliveryRate, DECIMAL(10,3))',
                                'ecpa1': 'CONVERT(designad_sitesdata.ecpa1 / designad_sitesdata.deliveryRate, DECIMAL(10,2))',
                                'ecpa2': 'CONVERT(designad_sitesdata.ecpa2 / designad_sitesdata.deliveryRate, DECIMAL(10,2))',
                                'ecpa3': 'CONVERT(designad_sitesdata.ecpa3 / designad_sitesdata.deliveryRate, DECIMAL(10,2))',
                                'ecpa4': 'CONVERT(designad_sitesdata.ecpa4 / designad_sitesdata.deliveryRate, DECIMAL(10,2))',
                            })
                    # 最近7天
                    elif str(gte_time).find(str(week_day)) >= 0 and str(lt_time).find(str(tomorrow)) >= 0:
                        if self.request.GET.get('_p_adCampaigns__id__exact') is not None:
                            qs = SitesTempData.objects.filter(user_id=self.user.id, type=1,
                                                              adCampaigns_id=self.request.GET.get(
                                                                  '_p_adCampaigns__id__exact'))
                            return qs
                        else:
                            qs = SitesTempData.objects.filter(user_id=self.user.id, type=1,
                                                              adCampaigns_id=first_ad[0].id)
                            return qs

                    # 本月
                    elif str(cur_date_first).find(str(gte_time)) >= 0 and str(tomorrow).find(str(lt_time)) >= 0:
                        if self.request.GET.get('_p_adCampaigns__id__exact') is not None:
                            qs = SitesTempData.objects.filter(user_id=self.user.id, type=2,
                                                              adCampaigns_id=int(self.request.GET.get('_p_adCampaigns__id__exact')))
                            return qs
                        else:
                            qs = SitesTempData.objects.filter(user_id=self.user.id, type=2,
                                                              adCampaigns_id=first_ad[0].id)
                            return qs
                    # 上一个月
                    elif str(gte_time).find(get_first_day_of_last_month()) >= 0 and str(lt_time).find(get_last_day_of_last_month()) >= 0:
                        if self.request.GET.get('_p_adCampaigns__id__exact') is not None:
                            qs = SitesTempData.objects.filter(user_id=self.user.id, type=4,
                                                              adCampaigns_id=int(
                                                                  self.request.GET.get('_p_adCampaigns__id__exact')))
                            return qs
                        else:
                            qs = SitesTempData.objects.filter(user_id=self.user.id, type=4,
                                                              adCampaigns_id=first_ad[0].id)
                            return qs
                    # 今年
                    elif str(day1).find(str(gte_time)) >= 0 and str(tomorrow).find(str(lt_time)) >= 0:
                        if self.request.GET.get('_p_adCampaigns__id__exact') is not None:
                            qs = SitesTempData.objects.filter(user_id=self.user.id, type=3,
                                                              adCampaigns_id=int(self.request.GET.get(
                                                                  '_p_adCampaigns__id__exact')))
                            return qs
                        else:
                            qs = SitesTempData.objects.filter(user_id=self.user.id, type=3,
                                                              adCampaigns_id=first_ad[0].id)
                            return qs
                    # 日期范围
                    else:
                        if self.request.GET.get('_p_adCampaigns__id__exact') is not None:
                            raw_list = sql_get_sites_date(self.user.id, self.request.GET.get('_p_adCampaigns__id__exact'),
                                                          gte_time, lt_time)
                            for d in raw_list:
                                is_data = SitesChoiseTempData.objects.filter(user_id=self.user.id, adCampaigns_id=self.request.GET.get('_p_adCampaigns__id__exact'),
                                                                       site_hostname=d.site_hostname, type=0)

                                if is_data.count() > 0:
                                    is_data[0].cost = d.cost
                                    is_data[0].clicks = d.clicks
                                    is_data[0].impressions = d.impressions
                                    is_data[0].ctr = d.ctr
                                    is_data[0].avgCpc = d.avgCpc
                                    is_data[0].avgCpm = d.avgCpm
                                    is_data[0].G1 = d.G1
                                    is_data[0].ecpa1 = d.ecpa1
                                    is_data[0].G2 = d.G2
                                    is_data[0].ecpa2 = d.ecpa2
                                    is_data[0].G3 = d.G3
                                    is_data[0].ecpa3 = d.ecpa3
                                    is_data[0].G4 = d.G4
                                    is_data[0].ecpa4 = d.ecpa4
                                    is_data[0].date = str(gte_time) + '/' + str(lt_time)
                                    is_data[0].save()

                                elif is_data.count() <= 0:
                                    sitesTempData = SitesChoiseTempData()
                                    sitesTempData.user_id = self.user.id
                                    sitesTempData.adCampaigns_id = d.adCampaigns_id
                                    sitesTempData.site_hostname = d.site_hostname
                                    sitesTempData.cost = d.cost
                                    sitesTempData.clicks = d.clicks
                                    sitesTempData.impressions = d.impressions
                                    sitesTempData.ctr = d.ctr
                                    sitesTempData.avgCpc = d.avgCpc
                                    sitesTempData.avgCpm = d.avgCpm
                                    sitesTempData.G1 = d.G1
                                    sitesTempData.ecpa1 = d.ecpa1
                                    sitesTempData.G2 = d.G2
                                    sitesTempData.ecpa2 = d.ecpa2
                                    sitesTempData.G3 = d.G3
                                    sitesTempData.ecpa3 = d.ecpa3
                                    sitesTempData.G4 = d.G4
                                    sitesTempData.ecpa4 = d.ecpa4
                                    sitesTempData.type = 0
                                    sitesTempData.date = str(gte_time) + '/' + str(lt_time)
                                    sitesTempData.save()

                            qs = SitesChoiseTempData.objects.filter(user_id=self.user.id, adCampaigns_id=self.request.GET.get(
                                '_p_adCampaigns__id__exact'), date=str(gte_time) + '/' + str(lt_time),  type=0)
                            return qs
                        else:
                            raw_list = sql_get_sites_date(self.user.id, first_ad[0].id,
                                                          gte_time, lt_time)
                            for d in raw_list:
                                is_data = SitesChoiseTempData.objects.filter(user_id=self.user.id,
                                                                       adCampaigns_id=first_ad[0].id,
                                                                       site_hostname=d.site_hostname, type=0)

                                if is_data.count() > 0:
                                    is_data[0].cost = d.cost
                                    is_data[0].clicks = d.clicks
                                    is_data[0].impressions = d.impressions
                                    is_data[0].ctr = d.ctr
                                    is_data[0].avgCpc = d.avgCpc
                                    is_data[0].avgCpm = d.avgCpm
                                    is_data[0].G1 = d.G1
                                    is_data[0].ecpa1 = d.ecpa1
                                    is_data[0].G2 = d.G2
                                    is_data[0].ecpa2 = d.ecpa2
                                    is_data[0].G3 = d.G3
                                    is_data[0].ecpa3 = d.ecpa3
                                    is_data[0].G4 = d.G4
                                    is_data[0].ecpa4 = d.ecpa4
                                    is_data[0].date = str(gte_time) + '/' + str(lt_time)
                                    is_data[0].save()
                                elif is_data.count() <= 0:

                                    sitesTempData = SitesChoiseTempData()
                                    sitesTempData.user_id = self.user.id
                                    sitesTempData.adCampaigns_id = first_ad[0].id
                                    sitesTempData.site_hostname = d.site_hostname
                                    sitesTempData.cost = d.cost
                                    sitesTempData.clicks = d.clicks
                                    sitesTempData.impressions = d.impressions
                                    sitesTempData.ctr = d.ctr
                                    sitesTempData.avgCpc = d.avgCpc
                                    sitesTempData.avgCpm = d.avgCpm
                                    sitesTempData.G1 = d.G1
                                    sitesTempData.ecpa1 = d.ecpa1
                                    sitesTempData.G2 = d.G2
                                    sitesTempData.ecpa2 = d.ecpa2
                                    sitesTempData.G3 = d.G3
                                    sitesTempData.ecpa3 = d.ecpa3
                                    sitesTempData.G4 = d.G4
                                    sitesTempData.ecpa4 = d.ecpa4
                                    sitesTempData.type = 0
                                    sitesTempData.date = str(gte_time) + '/' + str(lt_time)
                                    sitesTempData.save()

                            qs = SitesChoiseTempData.objects.filter(user_id=self.user.id, adCampaigns_id=first_ad[0].id, date=str(gte_time) + '/' + str(lt_time), type=0)
                            return qs

                else:
                    qs = SitesTempData.objects.filter(user_id=self.user.id, type=1)
                    return qs
        except Exception as e:
            print(e)

    # 屏蔽添加按钮
    def has_add_permission(self):
        return False

    def has_delete_permission(request, obj=None):
        return False

    def has_change_permission(request, obj=None):
            return False


class AdGoalsAdmin(object):
    list_display = ('name',  'order', 'coa',  'goalScript')
    list_filter = ('name',  'order', 'coa', 'goalScript')
    search_fields = ['user__username', 'name', 'order', 'coa', 'goalScript']
    # list_editable = ('goalScript')
    list_per_page = 20
    model_icon = 'fa fa-hand-o-up'
    show_bookmarks = False  # 屏蔽书签
    show_detail_fields = ['name']  # 显示数据详情
    ordering = ['order']
    exclude = ['user', 'goalId', 'idUser', 'seq', 'goalScript']  # 不显示列

    def save_models(self):
        obj = self.new_obj
        obj.user_id = self.user.id
        obj.save()
        name = obj.name
        order = obj.order
        coa = obj.coa

        log = Log(
            user_id=self.user.id,
            ip_addr=self.request.META['REMOTE_ADDR'],
            action_flag='create',
            message='新建目标追踪代码：{0}'.format(name)
        )
        log.save()
        # adGoals = AdGoals.objects.filter(name=AdGoals.objects.get(id=obj.id).name).count()
        if obj.goalId != '0':
            try:
                postOrUpdateGoal = HttpUtils.putHttp(Constants.GOALS_UPDATE_OR_DELETE_URL.format(AdGoals.objects.get(name=name).goalId), getPostGoals(name, order, coa), obj.user_id)
                if postOrUpdateGoal is not None:
                    print(postOrUpdateGoal)
                    print(postOrUpdateGoal.text)
                    if postOrUpdateGoal.status_code == 200:
                        obj.goalScript = '<!-- START MonAdvert  Goal Tag | ' + name + ' --><script type="text/javascript" src="http://service.monadvert.com/static/js/monadvert.js" data-goal="' + obj.goalId + '"></script><!-- END MonAdvert  Goal Tag | ' + name + ' -->'
                        AdGoals.objects.filter(id=obj.id).update(
                                                                goalId=obj.goalId,
                                                                goalScript=obj.goalScript)
            except Exception as e:
                print(e)
        elif obj.goalId == '0':
            try:
                postOrUpdateGoal = HttpUtils.postHttp(Constants.GOALS_URL, getPostGoals(name, order, coa), False,
                                                      obj.user_id)
                if postOrUpdateGoal is not None:
                    print(postOrUpdateGoal)
                    print(postOrUpdateGoal.text)
                    if postOrUpdateGoal.status_code == 200:
                        obj.goalId = postOrUpdateGoal.text.split(':')[1].split('}')[0].strip("\"")
                        obj.goalScript = '<!-- START MonAdvert  Goal Tag | ' + name + ' --><script type="text/javascript" src="http://service.monadvert.com/static/js/monadvert.js" data-goal="' + obj.goalId + '"></script><!-- END MonAdvert  Goal Tag | ' + name + ' -->'

                        AdGoals.objects.filter(id=obj.id).update(goalId=obj.goalId,
                                                                goalScript=obj.goalScript)
            except Exception as e:
                print(e)

    @receiver(pre_delete, sender=AdGoals)
    def ad_delte(sender, instance, **kwargs):
        try:
            isDel = HttpUtils.deleteHttp(Constants.GOALS_UPDATE_OR_DELETE_URL.format(instance.goalId),
                                         instance.user_id)
            if isDel is not None:
                print(isDel)
                print(isDel.text)
                if isDel.status_code == 200:
                    print('{0}删除成功！！！'.format(instance.goalId))
        except Exception as e:
            print(e)

    def queryset(self):
        qs = super(AdGoalsAdmin, self).queryset()
        if self.user.is_superuser:
            return qs
        else:
            myUser = UserProfile.objects.get(id=self.user.id)
            return qs.filter(user=myUser)


class DesignAdSiteAdmin(object):
    list_display = ('adName', 'sites')
    list_filter = ('adName', 'sites')
    search_fields = ['adName', 'sites']
    list_per_page = 20
    model_icon = 'fa fa-paper-plane-o'
    show_bookmarks = False  # 屏蔽书签

    exclude = ['adUser', 'adName', 'adStyle', 'keywords', 'impressionsEnabled', 'impressions', 'minutes', 'checked', 'maxDailyBudget', 'categoriesList', 'languagesList', 'browsersList', 'operatingSystemList', 'mobileCarriersList', 'devicesList', 'priceModel', 'price', 'geographicLocationList', 'languagesList', 'mondayList', 'categoriesList',
        'tuesdayList', 'wednesdayList', 'thursdayList', 'fridayList', 'saturdayList', 'sundayList', 'adAllTime', 'campaign_id', 'status', 'isMaxDailyBudget', 'variation_id', 'created_at', 'isPost', 'cost', 'clicks', 'ctr', 'cost_impressions', 'avgCpc', 'avgCpm', 'choise_time', 'updated_at']

    def queryset(self):
        qs = super(DesignAdSiteAdmin, self).queryset()
        if self.user.is_superuser:
            return qs
        else:
            myUser = UserProfile.objects.get(id=self.user.id)
            return qs.filter(adUser=myUser)

    def save_models(self):
        obj = self.new_obj
        obj.adUser_id = self.user.id
        obj.save()

        print('PUT')
        postOrUpdateSites.delay('PUT', obj.campaign_id, obj.sites, obj.adUser_id)

    def has_add_permission(self):
        return False

    def has_delete_permission(request, obj=None):
        return False


xadmin.site.register(DesignAdList, DesignAdAdmin)
xadmin.site.register(UserAdFileLibrary, UserAdFileLibraryAdmin)
xadmin.site.register(AdVariation, AdVariationAdmin)
# xadmin.site.register(DesignAdSite, DesignAdSiteAdmin)
xadmin.site.register(SitesData, SitesDataAdmin)
xadmin.site.register(AdGoals, AdGoalsAdmin)


def getPostGoals(name,order,coa):
    if coa is not None:
        postGoalJson = {
            'name':name,
            'order':order,
            # 'coa':coa
        }
        return postGoalJson
    else:
        postGoalJson = {
            'name': name,
            'order': order,
        }
        return postGoalJson


def sql_get_sites_date(userId, ad_id, gte_time, lt_time):
    if ad_id is not None:
        return SitesData.objects \
            .raw('select s.id,s.user_id,s.adCampaigns_id,s.site_hostname,'
                 'convert(SUM(s.cost/s.deliveryRate),decimal(10,2)) as cost,'
                 'SUM(s.clicks) as clicks, SUM(s.impressions) as impressions,'
                 'convert(case when SUM(s.impressions)<=0 then 0 else SUM(s.clicks)*100/SUM(s.impressions) end,decimal(10,2)) as ctr,'
                 'convert(case when SUM(s.clicks)<=0 then 0 else SUM(s.cost/s.deliveryRate)/SUM(s.clicks) end,decimal(10,3)) as avgCpc,'
                 'convert(case when SUM(s.impressions)<=0 then 0 else SUM(s.cost/s.deliveryRate)*1000/SUM(s.impressions) end,decimal(10,3)) as avgCpm,'
                 'convert(case when SUM(s.G1)<=0 then 0.00 else SUM(s.cost/s.deliveryRate)/SUM(s.G1) end,decimal(10,2)) as ecpa1,convert(SUM(s.G1),decimal(10,2)) as G1,'
                 'convert(case when SUM(s.G2)<=0 then 0.00 else SUM(s.cost/s.deliveryRate)/SUM(s.G2) end,decimal(10,2)) as ecpa2,convert(SUM(s.G2),decimal(10,2)) as G2,'
                 'convert(case when SUM(s.G3)<=0 then 0.00 else SUM(s.cost/s.deliveryRate)/SUM(s.G3) end,decimal(10,2)) as ecpa3,convert(SUM(s.G3),decimal(10,2)) as G3,'
                 'convert(case when SUM(s.G4)<=0 then 0.00 else SUM(s.cost/s.deliveryRate)/SUM(s.G4) end,decimal(10,2)) as ecpa4,convert(SUM(s.G4),decimal(10,2)) as G4  from designad_sitesdata s '
                 ' WHERE  s.user_id=' + str(userId)
                 + ' AND s.adCampaigns_id=' + str(ad_id)
                 + ' AND s.date BETWEEN "' + str(gte_time) + '" AND "' + str(lt_time)
                 + '" group by s.site_hostname,s.adCampaigns_id'
                 )


def get_first_day_of_last_month():
    d = datetime.datetime.now()

    year = d.year
    month = d.month

    if month == 1:
        month = 12
        year -= 1
    else:
        month -= 1
    return datetime.datetime(year, month, 1).strftime('%Y-%m-%d')


def get_last_day_of_last_month():
    d = datetime.datetime.now()
    year = d.year
    month = d.month
    if month == 1:
        month = 12
        year -= 1
    else:
        month -= 1
    days = calendar.monthrange(year, month)[1]
    return datetime.datetime(year, month, days).strftime('%Y-%m-%d')